
************CRSP MONTHLY***********
use PERMCO PERMNO TICKER NCUSIP RET RETX date PRC SHROUT DLRET DLRETX DLSTCD DISTCD EXCHCD SHRCD VOL BID ASK CFACPR using "$jfqa_rep/crsp_monthly_all.dta", clear 

qui{
rename PERMCO permco
rename PERMNO permno
rename TICKER ticker
rename NCUSIP ncusip
rename RET ret
rename RETX retx
rename PRC prc
rename SHROUT shrout
rename DLRET dlret
rename DLRETX dlretx
rename DLSTCD dlstcd
rename EXCHCD exchcd
rename SHRCD shrcd
rename VOL vol
rename BID bid 
rename ASK ask
rename CFACPR cfacpr

*define dates 
gen year=year(date)
gen month=month(date)
gen monthyear=ym(year, month)

*delete duplicates and clean prc
sort permno date
drop if date==date[_n-1] & permno==permno[_n-1]
replace prc=abs(prc)
gen prc_mid=(abs(bid/cfacpr)+abs(ask/cfacpr))/2

*define mktcap and age
sort permno monthyear
tsset permno monthyear
gen mktcap=prc*shrout
gen lagmktcap=L.mktcap
gen lag_prc_mid=L.prc_mid

by permno: gen age=_n

*de-listed returns
replace ret=dlret if missing(ret)
replace retx=dlretx if missing(retx)

qui{
foreach i in ret retx {
replace `i'=-0.3 if missing(`i') & dlstcd==500
replace `i'=-0.3 if missing(`i') & dlstcd==520
replace `i'=-0.3 if missing(`i') & dlstcd>550 & dlstcd<575
replace `i'=-0.3 if missing(`i') & dlstcd==580
replace `i'=-0.3 if missing(`i') & dlstcd==584
replace `i'=-1 if missing(`i') & ~missing(dlstcd)
}
}

*define returns
gen div_ret=(1+ret)/(1+retx)-1
gen mid_ret=(prc_mid/lag_prc_mid)-1+div_ret
gen ret_11_month=(1+L.ret)*(1+L2.ret)*(1+L3.ret)*(1+L4.ret)*(1+L5.ret)*(1+L6.ret)*(1+L7.ret)*(1+L8.ret)*(1+L9.ret)*(1+L10.ret)*(1+L11.ret)-1

keep permno permco ticker ncusip prc prc_mid month year monthyear ret div_ret mktcap lagmktcap ret_11_month exchcd shrcd age shrout mid_ret

order permno permco year month monthyear 
}

save "$jfqa_rep/10_CRSP_Controls.dta", replace

